// 缴费信息部分接口
var express = require('express');
var date = require("silly-datetime");
var router = express.Router();
var sqlUtils = require('../sql/sqlUtil');
let Return = require("../util/utils");
// 查询未审核信息|模糊查询
router.get('/', function (req, res, next) {
  let page = req.query.page - 1,
    limit = req.query.limit * 1,
    begin = page * limit,
    role = req.query.role,
    schools = req.query.schools;
  if (req.query.searchParams == undefined) {
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select count(1) as count from payment as a inner join student as b on a.stu_id=b.id and a.school in (${schools}) and a.is_del=0 and a.status=0`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.school in (${schools}) and a.is_del=0 and a.status=0 order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select count(1) as count from payment as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.status=0`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.status=0 order by 1 desc limit ?,?`;
    }
    // 获取数据总数
    sqlUtils.sqlConnect(sqlCount, (err, result) => {
      if (err) {
        console.log(err);
      } else {
        var count = result[0].count;
        sqlUtils.sqlConnect(sql, [begin, limit], function (err, data) {
          if (err) {
            console.log(err);
          } else {
            //   循环查询学生信息
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  } else {
    var result = JSON.parse(req.query.searchParams);
    var s_name = (result.school.length == 0) ? '%' : result.school,
      name = (result.name.length == 0) ? '%' : result.name,
      date_begin = result.date_begin,
      date_end = result.date_end,
      club = (result.club.length == 0) ? '%' : result.club;
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select a.id,b.name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.school in (${schools}) and a.is_del=0 and a.status=0`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.school in (${schools}) and a.is_del=0 and a.status=0 order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select a.id,b.name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.is_del=0 and a.status=0`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.is_del=0 and a.status=0 order by 1 desc limit ?,?`;
    }
    // 计算表中符合条件信息数量
    sqlUtils.sqlConnect(sqlCount, [date_begin, date_end], function (err, data) {
      if (err) {
        console.log(err);
      } else {
        var count = data.length;
        sqlUtils.sqlConnect(sql, [date_begin, date_end, begin, limit], function (err, data) {
          if (err) {
            console.log(err)
          } else {
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  }
});
// 查询审核通过信息|模糊查询
router.get('/pass', function (req, res, next) {
  let page = req.query.page - 1,
    limit = req.query.limit * 1,
    begin = page * limit,
    role = req.query.role,
    schools = req.query.schools;
  if (req.query.searchParams == undefined) {
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select count(1) as count from payment as a inner join student as b on a.stu_id=b.id and a.school in (${schools}) and a.is_del=0 and a.status=1`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.school in (${schools}) and a.is_del=0 and a.status=1 order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select count(1) as count from payment as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.status=1`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.status=1 order by 1 desc limit ?,?`;
    }
    // 获取数据总数
    sqlUtils.sqlConnect(sqlCount, (err, result) => {
      if (err) {
        console.log(err);
      } else {
        var count = result[0].count;
        sqlUtils.sqlConnect(sql, [begin, limit], function (err, data) {
          if (err) {
            console.log(err);
          } else {
            //   循环查询学生信息
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  } else {
    var result = JSON.parse(req.query.searchParams);
    var s_name = (result.school.length == 0) ? '%' : result.school,
      name = (result.name.length == 0) ? '%' : result.name,
      date_begin = result.date_begin,
      date_end = result.date_end,
      club = (result.club.length == 0) ? '%' : result.club;
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select a.id,b.name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.school in (${schools}) and a.is_del=0 and a.status=1`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.school in (${schools}) and a.is_del=0 and a.status=1 order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select a.id,b.name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.is_del=0 and a.status=1`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.is_del=0 and a.status=1 order by 1 desc limit ?,?`;
    }
    // 计算表中符合条件信息数量
    sqlUtils.sqlConnect(sqlCount, [date_begin, date_end], function (err, data) {
      if (err) {
        console.log(err);
      } else {
        var count = data.length;
        sqlUtils.sqlConnect(sql, [date_begin, date_end, begin, limit], function (err, data) {
          if (err) {
            console.log(err)
          } else {
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  }
});
// 查询审核失败信息|模糊查询
router.get('/failed', function (req, res, next) {
  let page = req.query.page - 1,
    limit = req.query.limit * 1,
    begin = page * limit,
    role = req.query.role,
    schools = req.query.schools;
  if (req.query.searchParams == undefined) {
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select count(1) as count from payment as a inner join student as b on a.stu_id=b.id and a.school in (${schools}) and a.is_del=0 and a.status=2`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.school in (${schools}) and a.is_del=0 and a.status=2 order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select count(1) as count from payment as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.status=2`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.status=2 order by 1 desc limit ?,?`;
    }
    // 获取数据总数
    sqlUtils.sqlConnect(sqlCount, (err, result) => {
      if (err) {
        console.log(err);
      } else {
        var count = result[0].count;
        sqlUtils.sqlConnect(sql, [begin, limit], function (err, data) {
          if (err) {
            console.log(err);
          } else {
            //   循环查询学生信息
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  } else {
    var result = JSON.parse(req.query.searchParams);
    var s_name = (result.school.length == 0) ? '%' : result.school,
      name = (result.name.length == 0) ? '%' : result.name,
      date_begin = result.date_begin,
      date_end = result.date_end,
      club = (result.club.length == 0) ? '%' : result.club;
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select a.id,b.name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.school in (${schools}) and a.is_del=0 and a.status=2`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.school in (${schools}) and a.is_del=0 and a.status=2 order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select a.id,b.name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.is_del=0 and a.status=2`;
      sql = `select a.id,a.club,a.amount,a.pay_time,a.pay_img_url,a.tip,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from payment as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.pay_time between ? and ? and a.is_del=0 and a.status=2 order by 1 desc limit ?,?`;
    }
    // 计算表中符合条件信息数量
    sqlUtils.sqlConnect(sqlCount, [date_begin, date_end], function (err, data) {
      if (err) {
        console.log(err);
      } else {
        var count = data.length;
        sqlUtils.sqlConnect(sql, [date_begin, date_end, begin, limit], function (err, data) {
          if (err) {
            console.log(err)
          } else {
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  }
});
// 审核
router.put('/check', (req, res) => {
  const id = req.body.id,
    name = req.body.staff_name,
    flag = req.body.flag;
  if (name) {
    sqlUtils.sqlConnect("update payment set status=?,staff_name=? where id=?", [flag, name, id], (err) => {
      if (err) {
        res.send(Return.setResult(500, "系统错误，请稍后重试!"))
      } else {
        res.send(Return.setResult())
      }
    })

  }
})
// 删除审核失败信息
router.delete('/delete', (req, res) => {
  const id = req.body.id,
    name = req.body.staff_name;
  if (name) {
    sqlUtils.sqlConnect("update payment set is_del=1,staff_name=? where id=?", [name, id], (err) => {
      if (err) {
        res.send(Return.setResult(500, "系统错误，请稍后重试!"))
      } else {
        res.send(Return.setResult())
      }
    })
  }
})
module.exports = router;